/*TABLE DAO COUNT INFO*/
delete from TBL_DAO_INFO

INSERT INTO TBL_DAO_INFO
SELECT B.DAO ,COUNT(CIF) AS Total_Customer, 0,0,0
FROM  TBL_CUSTOMER AS B
WHERE DAO IS NOT NULL
 GROUP BY  B.DAO

 --select * from TBL_DAO_INFO
 
insert into TBL_DAO_INFO
select a.DAO_NAME, (select COUNT(a.CIF) from TBL_CUSTOMER a, TBL_BRANCH b where a.BRANCH_ID= b.BRANCH_ID) AS Total_Customer, 0,0,0
from TBL_HR as a
where DAO is not null and a.group_id = 1

insert into TBL_DAO_INFO
select a.DAO_NAME, b.num_cif, 0 ,0,0
from TBL_HR a, (select count(a.CIF) as num_cif, b.REGION from TBL_CUSTOMER a, TBL_BRANCH b where a.BRANCH_ID= b.BRANCH_ID  group by b.REGION) b
where a.BRANCH_CODE_SME = b.REGION

insert into TBL_DAO_INFO
select a.USER_NAME, b.num_cif, 0 ,0,0
from TBL_HR a, (select count(a.CIF) as num_cif, b.BRANCH_CODE_SME from TBL_CUSTOMER a, TBL_BRANCH b where a.BRANCH_ID= b.BRANCH_ID  group by b.BRANCH_CODE_SME) b
where a.BRANCH_CODE_SME = b.BRANCH_CODE_SME and a.group_id = 3

update a
set a.New_Customer = c.num_cif
from TBL_DAO_INFO a,(SELECT B.DAO ,COUNT(CIF) AS num_cif FROM  TBL_CUSTOMER AS B WHERE DAO IS NOT NULL and CUS_OPEN_DATE > (SELECT DATEADD(m,-1,GETDATE()) ) GROUP BY  B.DAO) c
where a.Dao collate database_default = c.DAO

update a
set a.New_Customer = (select count(cif) from TBL_CUSTOMER where DAO IS NOT NULL and CUS_OPEN_DATE > (SELECT DATEADD(m,-1,GETDATE()) ))
from TBL_DAO_INFO a, TBL_HR b
where a.Dao collate database_default = b.DAO_NAME and b.group_id = 1

update a
set a.New_Customer = c.num_cif
from TBL_DAO_INFO a, TBL_HR b, (select count(a.CIF) as num_cif, b.REGION from TBL_CUSTOMER a, TBL_BRANCH b where a.BRANCH_ID= b.BRANCH_ID and a.CUS_OPEN_DATE > (SELECT DATEADD(m,-1,GETDATE()) ) group by b.REGION) c
where a.Dao collate database_default = b.DAO_NAME and b.group_id = 2 and c.REGION = b.BRANCH_CODE_SME

update c
set c.New_Customer = b.num_cif
from TBL_DAO_INFO c, TBL_HR a, (select count(a.CIF) as num_cif, b.BRANCH_CODE_SME from TBL_CUSTOMER a, TBL_BRANCH b where a.BRANCH_ID= b.BRANCH_ID and a.CUS_OPEN_DATE > (SELECT DATEADD(m,-1,GETDATE()) ) group by b.BRANCH_CODE_SME) b
where c.Dao collate database_default = a.USER_NAME and a.BRANCH_CODE_SME = b.BRANCH_CODE_SME and a.group_id = 3
--select * from TBL_DAO_INFO